namespace CISLib
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class ManageCompany
    {
        public static DataSet GetCompany()
        {
            string strSQL = "Select * from TB_CompanyInfo order by CATId,RegionId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataSet GetCompany(Guid areaId)
        {
            string strSQL = "Select * from TB_CompanyInfo where AreaId=@AreaId order by CATId,RegionId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static SqlDataReader GetCompany(Guid catId, Guid regionId)
        {
            string strSQL;
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            if ((catId.ToString() == "00000000-0000-0000-0000-000000000000") && (regionId.ToString() == "00000000-0000-0000-0000-000000000000"))
            {
                strSQL = "Select * from TB_CompanyInfo";
            }
            else if (catId.ToString() == "00000000-0000-0000-0000-000000000000")
            {
                MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                strSQL = "Select * from TB_CompanyInfo where RegionId=@RegionId";
            }
            else if (regionId.ToString() == "00000000-0000-0000-0000-000000000000")
            {
                MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                strSQL = "Select * from TB_CompanyInfo where CATId=@CATId";
            }
            else
            {
                strSQL = "Select * from TB_CompanyInfo where CATId = @CATId and RegionId = @RegionId";
                MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            }
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static DataSet GetCompany(Guid areaId, string key)
        {
            string strSQL = "Select TB_CompanyInfo.* from TB_CompanyInfo left outer join TB_Region on TB_CompanyInfo.RegionId = TB_Region.RegionId where TB_CompanyInfo.AreaId=@AreaId and TB_CompanyInfo.CompanyName like @Key order by TB_Region.RegionName,CATId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters("%" + key + "%", "Key", SqlDbType.VarChar);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataSet GetCompany(Guid catId, Guid regionId, Guid areaId)
        {
            string strSQL = "Select * from TB_CompanyInfo where CATId=@CATId  and RegionId=@RegionId and AreaId=@AreaId order by CATId,RegionId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataSet GetCompany(Guid areaId, string key, Guid catId)
        {
            string strSQL = "Select TB_CompanyInfo.* from TB_CompanyInfo left outer join TB_Region on TB_CompanyInfo.RegionId = TB_Region.RegionId where TB_CompanyInfo.AreaId=@AreaId and TB_CompanyInfo.CATId=@CATId  and TB_CompanyInfo.CompanyName like @Key order by TB_Region.RegionName,CATId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters("%" + key + "%", "Key", SqlDbType.VarChar);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataSet GetCompany(Guid areaId, Guid catId, string key, Guid regionId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Select TB_CompanyInfo.* from TB_CompanyInfo left outer join TB_Region on TB_CompanyInfo.RegionId = TB_Region.RegionId ");
            sb.Append(" where TB_CompanyInfo.AreaId=@AreaId and TB_CompanyInfo.CompanyName like @Key ");
            if (catId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append(" and TB_CompanyInfo.CATId=@CATId ");
            }
            if (regionId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append(" and TB_CompanyInfo.RegionId=@RegionId ");
            }
            sb.Append(" order by TB_Region.RegionName,CATId");
            string strSQL = sb.ToString();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters("%" + key + "%", "Key", SqlDbType.VarChar);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataSet GetCompany(Guid areaId, string key, Guid catId, Guid regionId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Select TB_CompanyInfo.* from TB_CompanyInfo left outer join TB_Region on TB_CompanyInfo.RegionId = TB_Region.RegionId where TB_CompanyInfo.AreaId=@AreaId and TB_CompanyInfo.CATId=@CATId ");
            if (regionId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append(" and TB_CompanyInfo.RegionId=@RegionId ");
            }
            sb.Append(" and TB_CompanyInfo.CompanyName like @Key order by TB_Region.RegionName,CATId");
            string strSQL = sb.ToString();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters("%" + key + "%", "Key", SqlDbType.VarChar);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static Company[] GetCompanyAryViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            ArrayList list = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CompanyInfo where RegionId = @RegionId";
            MyDB.AddParameters(id, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                Company company = new Company();
                company.CompanyId = MyReader.GetGuid(0);
                company.CompanyName = MyReader.GetString(1);
                company.CompanyAdd = MyReader.GetString(2);
                company.CompanyTel = MyReader.GetString(3);
                company.CompanyFax = MyReader.GetString(4);
                company.CompanyWebSite = MyReader.GetString(5);
                company.CATtId = MyReader.GetGuid(6);
                company.RegionId = MyReader.GetGuid(7);
                company.AreaId = MyReader.GetGuid(8);
                company.CompanyZip = MyReader.GetString(9);
                try
                {
                    company.UserId = MyReader.GetGuid(10);
                }
                catch
                {
                    Guid uid = new Guid();
                    company.UserId = uid;
                }
                list.Add(company);
            }
            MyDB.DBClose();
            return (Company[]) list.ToArray(typeof(Company));
        }

        public static DataSet GetCompanyForDown(Guid areaId, Guid catId, string key, Guid regionId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Select TB_CompanyInfo.CompanyName,TB_CompanyInfo.CompanyAdd,TB_CompanyInfo.CompanyTel,TB_CompanyInfo.CompanyFax,TB_CompanyInfo.CompanyWebsite,TB_Region.RegionName,TB_Region.RegionSCName,TB_CATGroup.CATGroupName ");
            sb.Append("\tfrom TB_CompanyInfo left outer join TB_Region on TB_CompanyInfo.RegionId = TB_Region.RegionId left outer join TB_CATGroup on TB_CompanyInfo.CATId = TB_CATGroup.CATGroupId  ");
            sb.Append(" where TB_CompanyInfo.AreaId=@AreaId and TB_CompanyInfo.CompanyName like @Key ");
            if (catId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append(" and TB_CompanyInfo.CATId=@CATId ");
            }
            if (regionId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append(" and TB_CompanyInfo.RegionId=@RegionId ");
            }
            sb.Append(" order by TB_Region.RegionName,CATId");
            string strSQL = sb.ToString();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters("%" + key + "%", "Key", SqlDbType.VarChar);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataSet GetCompanyForDown(Guid areaId, string key, Guid catId, Guid regionId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Select TB_CompanyInfo.CompanyName,TB_CompanyInfo.CompanyAdd,TB_CompanyInfo.CompanyTel,TB_CompanyInfo.CompanyFax,TB_CompanyInfo.CompanyWebsite,TB_Region.RegionName,TB_Region.RegionSCName,TB_CATGroup.CATGroupName ");
            sb.Append("\tfrom TB_CompanyInfo left outer join TB_Region on TB_CompanyInfo.RegionId = TB_Region.RegionId left outer join TB_CATGroup on TB_CompanyInfo.CATId = TB_CATGroup.CATGroupId  ");
            sb.Append("\twhere TB_CompanyInfo.AreaId=@AreaId and TB_CompanyInfo.CATId=@CATId ");
            if (regionId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append(" and TB_CompanyInfo.RegionId=@RegionId ");
            }
            sb.Append(" and TB_CompanyInfo.CompanyName like @Key order by TB_Region.RegionName,CATId");
            string strSQL = sb.ToString();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters("%" + key + "%", "Key", SqlDbType.VarChar);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataSet GetCompanyInfoViaUserId(Guid userId)
        {
            string strSQL = "Select * from TB_CompanyInfo where UserId=@UserId order by CATId,RegionId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static Company GetCompanyViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            Company company = new Company();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CompanyInfo where CompanyId = @CompanyId";
            MyDB.AddParameters(id, "CompanyId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                company.CompanyId = MyReader.GetGuid(0);
                company.CompanyName = MyReader.GetString(1);
                company.CompanyAdd = MyReader.GetString(2);
                company.CompanyTel = MyReader.GetString(3);
                company.CompanyFax = MyReader.GetString(4);
                company.CompanyWebSite = MyReader.GetString(5);
                company.CATtId = MyReader.GetGuid(6);
                company.RegionId = MyReader.GetGuid(7);
                company.AreaId = MyReader.GetGuid(8);
                company.CompanyZip = MyReader.GetString(9);
                try
                {
                    company.UserId = MyReader.GetGuid(10);
                }
                catch
                {
                    company.UserId = new Guid();
                }
            }
            MyDB.DBClose();
            return company;
        }

        public static void InsertNewCompany(string strCompanyName, string strCompanyAdd, string strCompanyTel, string strCompanyFax, string strCompanyWebsite, Guid catId, Guid regionId, Guid areaId, string companyZip, Guid userId)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(id, "CompanyId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(strCompanyName, "CompanyName", SqlDbType.VarChar);
                MyDB.AddParameters(strCompanyAdd, "CompanyAdd", SqlDbType.VarChar);
                MyDB.AddParameters(strCompanyTel, "CompanyTel", SqlDbType.VarChar);
                MyDB.AddParameters(strCompanyFax, "CompanyFax", SqlDbType.VarChar);
                MyDB.AddParameters(strCompanyWebsite, "CompanyWebsite", SqlDbType.VarChar);
                MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(companyZip, "CompanyZip", SqlDbType.VarChar);
                MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
                string strSQL = "Insert into TB_CompanyInfo (CompanyId,CompanyName,CompanyAdd,CompanyTel,CompanyFax,CompanyWebsite,CATId,RegionId,AreaId,CompanyZip,UserId) values (@CompanyId,@CompanyName,@CompanyAdd,@CompanyTel,@CompanyFax,@CompanyWebsite,@CATId,@RegionId,@AreaId,@CompanyZip,@UserId)";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.DBTransactionCommit();
            }
            catch (Exception)
            {
                MyDB.DBTransactionRollback();
            }
            MyDB.DBClose();
        }

        public static bool IsExistSameCompanyName(string strCompanyName)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(strCompanyName, "CompanyName", SqlDbType.VarChar);
            string strSQL = "select * from TB_CompanyInfo where CompanyName = @CompanyName";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveCompanyViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(id, "CompanyId", SqlDbType.UniqueIdentifier);
                string strSQL = "Delete From TB_CompanyInfo where CompanyId = @CompanyId";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CompanyId", SqlDbType.UniqueIdentifier);
                strSQL = "Delete From TB_CustomerOrgTree where CompanyId = @CompanyId";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.DBTransactionCommit();
            }
            catch (Exception)
            {
                MyDB.DBTransactionRollback();
            }
            MyDB.DBClose();
        }

        public static void UpdateCompany(Guid companyId, string strCompanyName, string strCompanyAdd, string strCompanyTel, string strCompanyFax, string strCompanyWebsite, Guid catId, Guid regionId, Guid areaId, string companyZip)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(strCompanyName, "CompanyName", SqlDbType.VarChar);
                MyDB.AddParameters(strCompanyAdd, "CompanyAdd", SqlDbType.VarChar);
                MyDB.AddParameters(strCompanyTel, "CompanyTel", SqlDbType.VarChar);
                MyDB.AddParameters(strCompanyFax, "CompanyFax", SqlDbType.VarChar);
                MyDB.AddParameters(strCompanyWebsite, "CompanyWebsite", SqlDbType.VarChar);
                MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(companyId, "CompanyId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(companyZip, "CompanyZip", SqlDbType.VarChar);
                string strSQL = "Update TB_CompanyInfo set CompanyName = @CompanyName,CompanyAdd = @CompanyAdd,CompanyTel = @CompanyTel,CompanyFax = @CompanyFax,CompanyWebsite=@CompanyWebsite,CATId = @CATId,RegionId = @RegionId,AreaId=@AreaId,CompanyZip=@CompanyZip where CompanyId = @CompanyId";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(strCompanyName, "NodeName", SqlDbType.VarChar);
                MyDB.AddParameters(companyId, "NodeId", SqlDbType.UniqueIdentifier);
                strSQL = "Update TB_CustomerOrgTree set NodeName = @NodeName where NodeId = @NodeId";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.DBTransactionCommit();
            }
            catch (Exception)
            {
                MyDB.DBTransactionRollback();
            }
            MyDB.DBClose();
        }

        public static void UpdateCompanyEditorViaId(Guid companyId, Guid userId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(companyId, "CompanyId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
                string strSQL = "Update TB_CompanyInfo set UserId=@UserId where CompanyId = @CompanyId";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.DBTransactionCommit();
            }
            catch (Exception ex)
            {
                MyDB.DBTransactionRollback();
                MyDB.DBClose();
                throw ex;
            }
            MyDB.DBClose();
        }
    }
}

